/***********************************************************************************************************************************
 *    Name: WhoIsActive.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
	EXECUTE dbo.sp_WhoIsActive @help= 1;
**/
USE master;
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'DBAdmin') RAISERROR (N'Schema DBAdmin Not Found', 19, 1) WITH LOG;
IF OBJECT_ID(N'DBAdmin.WhoIsActive') IS NOT NULL DROP PROCEDURE DBAdmin.WhoIsActive;
GO

CREATE PROCEDURE DBAdmin.WhoIsActive
	@filter					sysname			= '',
	@filter_type			VARCHAR(10)		= 'session',
	@not_filter				sysname			= '',
	@not_filter_type		VARCHAR(10)		= 'session',
	@show_own_spid			BIT				= 0,
	@show_system_spids		BIT				= 0,
	@show_sleeping_spids	TINYINT			= 2,
	@get_full_inner_text	BIT				= 1,	-- [sql_text]
	@get_plans				TINYINT			= 1,	-- [query_plan]
	@get_outer_command		BIT				= 1,	-- [sql_command] (batch)
	@get_transaction_info	BIT				= 1,	-- [tran_log%]
	@get_task_info			TINYINT			= 2,	-- [tasks][wait_info][physical%][context%][block%]
	@get_locks				BIT				= 1,	-- [locks]
	@get_avg_time			BIT				= 0,	-- [dd hh:mm:ss.mss (avg)]
	@get_additional_info	BIT				= 0,	-- 
	@find_block_leaders		BIT				= 0,	-- 
	@delta_interval			TINYINT			= 0,	-- [%_delta]
	@output_column_list		VARCHAR(8000)	= '[status][session_id][blocking_session_id][%]',
	@sort_order				VARCHAR(500)	= '',
	@format_output			TINYINT			= 0,	-- XML: [sql_text],[sql_command]
	@destination_table		VARCHAR(4000)	= '[##WhoIsActive]',
	@schema					VARCHAR(MAX)	= NULL		AS
BEGIN TRY EXECUTE ('DROP TABLE ' + @destination_table) END TRY BEGIN CATCH END CATCH;
EXECUTE dbo.sp_WhoIsActive @filter,@filter_type,@not_filter,@not_filter_type,@show_own_spid,@show_system_spids,@show_sleeping_spids,@get_full_inner_text,@get_plans,@get_outer_command,@get_transaction_info,@get_task_info,@get_locks,@get_avg_time,@get_additional_info,@find_block_leaders,@delta_interval,@output_column_list,@sort_order,@format_output,@destination_table,1,@schema OUTPUT;
SET @schema= REPLACE(@schema, N'<table_name>', @destination_table); EXECUTE (@schema);
EXECUTE dbo.sp_WhoIsActive @filter,@filter_type,@not_filter,@not_filter_type,@show_own_spid,@show_system_spids,@show_sleeping_spids,@get_full_inner_text,@get_plans,@get_outer_command,@get_transaction_info,@get_task_info,@get_locks,@get_avg_time,@get_additional_info,@find_block_leaders,@delta_interval,@output_column_list,@sort_order,@format_output,@destination_table,0,@schema       ;
SELECT b.chain, a.* FROM ##WhoIsActive a LEFT JOIN DBAdmin.ShowBlockingChain b ON (a.session_id = b.session_id) ORDER BY a.status, COALESCE(b.chain, a.session_id);
EXECUTE ('DROP TABLE ' + @destination_table);
GO